<?php
/**
 * Created by JetBrains PhpStorm.
 * User: AndreyG
 * Date: 25.01.12
 * Time: 15:01
 * To change this template use File | Settings | File Templates.
 */

require_once('inc/app.php');

$params = $_GET;
$where = array();

// Filter by category
if($params['cat']) {
    settype($params['cat'], int);
    $where[] = "company_has_category.category_id = {$params['cat']}";
}

// Filter by region
if($params['location']) {
    settype($params['location'], int);
    $where[] = "company.location_id = {$params['location']}";
}

// Create 'order by' condition
switch($params['sort']) {
    case 'name':
        $orderField = 'company.name';
    break;
    case 'rating':
        $orderField = 'company_rating.rating';
    break;
    default:
        $orderField = 'company.name';
}

if(!$params['dir'] || $params['dir'] == 'asc') {
    $orderDir = 'ASC';
} else {
    $orderDir = 'DESC';
}

// Create 'where' condition
if(count($where) > 0) {
    $whereTerm = implode(' AND ', $where);
} else {
    $whereTerm = 1;
}
// Load company
$companyCollection = getRowSet($dbConnect->query(
    "SELECT company.*, location.title AS location, company_rating.rating AS rating, GROUP_CONCAT(' ',category.title) AS category
    FROM company
    LEFT JOIN location ON company.location_id = location.id
    LEFT JOIN company_has_category ON company.id = company_has_category.company_id
    LEFT JOIN category ON category.id = company_has_category.category_id
    LEFT JOIN company_rating ON company.id = company_rating.company_id
    WHERE {$whereTerm}
    GROUP BY company.id
    ORDER BY {$orderField} {$orderDir};"
    )
);

// Load category
$locationCollection = getRowSet($dbConnect->query(
        "SELECT * FROM location"
    )
);

// Load category
$categoryCollection = getRowSet($dbConnect->query(
        "SELECT * FROM category"
    )
);

ob_end_flush();
include 'template/list.php';

?>